/*
****************************************************************************************************************************
*/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('MarketEntityUpdate') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	PRINT 'Droping procedure dbo.MarketEntityUpdate'
	DROP PROCEDURE dbo.MarketEntityUpdate
END
GO

PRINT 'Creating procedure dbo.MarketEntityUpdate'
GO

CREATE PROCEDURE dbo.MarketEntityUpdate
AS
BEGIN
	-- Start code added temporarily for LINQ (Auto-generated Type) 
	SET FMTONLY OFF;
	-- End code added temporarily

	UPDATE Ord SET OperationPos = PositionCalc.RowNumber
	FROM dbo.[Order] Ord
	JOIN 
	(
		SELECT 
			Ent.MarketEntityId, 
			ROW_NUMBER() OVER (PARTITION BY Ent.MarketEntityId, Ord.Operation 
			ORDER BY OrderPrice DESC, OrderDate) AS RowNumber,
			Ord.OrderPrice,
			Ord.OrderId
		FROM dbo.[Order] Ord
		JOIN dbo.MarketEntity Ent ON Ord.MarketEntityId = Ent.MarketEntityId
		WHERE Ord.[Status] = 0 -- Active
		AND Ord.Operation = 0 -- Bid
	) AS PositionCalc ON PositionCalc.OrderId = Ord.OrderId
	WHERE Ord.Operation = 0 -- Bid
	
	UPDATE Ord SET OperationPos = PositionCalc.RowNumber
	FROM dbo.[Order] Ord
	JOIN 
	(
		SELECT 
			Ent.MarketEntityId, 
			ROW_NUMBER() OVER (PARTITION BY Ent.MarketEntityId, Ord.Operation 
			ORDER BY OrderPrice ASC, OrderDate) AS RowNumber,
			Ord.OrderPrice,
			Ord.OrderId
		FROM dbo.[Order] Ord
		JOIN dbo.MarketEntity Ent ON Ord.MarketEntityId = Ent.MarketEntityId
		WHERE Ord.[Status] = 0 -- Active
		AND Ord.Operation = 1 -- ASK  
	) AS PositionCalc ON PositionCalc.OrderId = Ord.OrderId
	WHERE  Ord.Operation = 1 -- ASK  

	UPDATE Ord SET Spread = SpreadCalc.Spread
	FROM dbo.[Order] Ord
	JOIN
	(
		SELECT OrdCalc.OrderId, OfferIsNull.OrderPrice,
			ABS(OrdCalc.OrderPrice - ISNULL(OfferIsNull.OrderPrice,0)) AS Spread
			--ABS(OrdCalc.OrderPrice-ISNULL(Offer.OrderPrice,ISNULL(OfferIsNull.OrderPrice,0))) AS Spread
		FROM dbo.[Order] OrdCalc
		LEFT OUTER JOIN 
		(
			SELECT 
				Ent.MarketEntityId,
				OrdCalcInner.OrderId,
				OrdCalcInner.OrderPrice,
				OrdCalcInner.Operation,
				OrdCalcInner.OperationPos
			FROM dbo.[Order] OrdCalcInner
			JOIN dbo.MarketEntity Ent ON OrdCalcInner.MarketEntityId = Ent.MarketEntityId
			WHERE OrdCalcInner.[Status] = 0 
		) AS Offer ON OrdCalc.MarketEntityId = Offer.MarketEntityId 
			AND OrdCalc.OperationPos = Offer.OperationPos AND OrdCalc.Operation != Offer.Operation
		LEFT OUTER JOIN 
		(
			SELECT 
				OrdCalcInner.MarketEntityId,
				OrdCalcInner.OrderPrice,
				OrdCalcInner.Operation,
				OrdCalcInner.OperationPos
			FROM dbo.[Order] OrdCalcInner
			JOIN 
			(
				SELECT 
					OrderIsNullInner.MarketEntityId,OrderIsNullInner.Operation,MIN(OrderIsNullInner.OperationPos) AS OperationPos
				FROM dbo.[Order] OrderIsNullInner
				WHERE OrderIsNullInner.[Status] = 0 
				GROUP BY OrderIsNullInner.MarketEntityId,OrderIsNullInner.Operation
				
			) AS OrderIsNull ON OrdCalcInner.MarketEntityId = OrderIsNull.MarketEntityId AND OrdCalcInner.Operation != OrderIsNull.Operation
				AND OrdCalcInner.OperationPos = OrderIsNull.OperationPos
			WHERE OrdCalcInner.[Status] = 0 -- Active
		) AS OfferIsNull ON OrdCalc.MarketEntityId = OfferIsNull.MarketEntityId AND OrdCalc.Operation != OfferIsNull.Operation
		WHERE OrdCalc.[Status] = 0 -- Active		
	) AS SpreadCalc ON Ord.OrderId = SpreadCalc.OrderId
	WHERE Ord.[Status] = 0 -- Active
		
	DECLARE @currentDate DATETIME
	SET @currentDate = GETDATE()
	EXEC dbo.PreparePEXIndex @processDate = @currentDate
END
GO